package iSOCK::Model::App;

use DateTime;
use DateTime::Duration::Fuzzy qw( time_ago );
use DateTime::Format::ISO8601;
use List::MoreUtils qw( mesh uniq );
use iSOCK;
use SOCK;
use SOCK::BOT qw( add_work_sheet ci_svn_tail last_breath lol_as_tat normalized_version_string sanitized_hash sock_version_string supported_device_name );
use SOCK::PUB;
use SOCK::SVN::Component;
use SOCK::SVN::Product;
use Sort::Naturally qw( ncmp nsort );
use Spreadsheet::WriteExcel;
use Tie::FlipFlop;
use Try::Tiny;

use strict;
use base 'Catalyst::Model::DBIC::Schema';

my $dsn = $ENV{ISOCK_TEST_APP_DSN} || ( 'dbi:Pg:dbname=' . ( $ENV{PGDATABASE} || 'app' )
. ( ';user='     . ( iSOCK->config->{database_user}     || $ENV{PGUSER}     ) )
. ( ';password=' . (                                       $ENV{PGPASSWORD} ) )
);

__PACKAGE__->config (
  schema_class => 'iSOCK::Schema::App',
  connect_info => { dsn => $dsn, quote_names => 1 },
);

=head1 NAME

iSOCK::Model::App - Catalyst DBIC Schema Model

=head1 SYNOPSIS

See L<iSOCK>

=head1 DESCRIPTION

L<Catalyst::Model::DBIC::Schema> Model using schema L<iSOCK::Schema::App>

=head1 GENERATED BY

Catalyst::Helper::Model::DBIC::Schema - 0.55

=head1 AUTHOR

Design Flow user

=head1 LICENSE

This library is free software, you can redistribute it and/or modify
it under the same terms as Perl itself.

=cut

sub not_bool {
  my ( $self, $column ) = @_;
  
  return -or => [ -not_bool => $column, $column => undef ];
}

sub bug_tracker_dsn {
  my ( $self, $type ) = @_;
  
  if ( my $component_type_r = $self->resultset ( 'ComponentType' )->find ( $type ) ) {
    return $component_type_r->bug_tracker_dsn;
  }
  
  return;
}

sub _as_loh {
  my ( $self, $table, $c ) = @_;
  
  my @loh = sort { ncmp ( $a->{id}, $b->{id} ) } $self->resultset ( $table )->search ( undef, { result_class => 'DBIx::Class::ResultClass::HashRefInflator' } );
  
  if ( $c && ( $table eq 'Datasheet' || $table eq 'Specification' ) ) {
    if ( my $user_r = $self->user_r ( $c ) ) {
      for ( @loh ) {
        next unless my $whatever_r = $self->resultset ( $table )->find ( $_->{id} );

        next unless $whatever_r->secure;

        next if     $self->resultset ( "UserTo$table" )->find ( $user_r->id, $_->{id}, 'owner'    );
        next if     $self->resultset ( "UserTo$table" )->find ( $user_r->id, $_->{id}, 'writer'   );
        next if     $self->resultset ( "UserTo$table" )->find ( $user_r->id, $_->{id}, 'follower' );
        
        $_->{hidden} = 1;
      }
    }
  }
  
  return [ grep { !$_->{hidden} } @loh ];
}

sub components_loh          { my ( $self ) = @_ ; return $self->_as_loh ( 'Component'         ) }
sub component_types_loh     { my ( $self ) = @_ ; return $self->_as_loh ( 'ComponentType'     ) }
sub processes_loh           { my ( $self ) = @_ ; return $self->_as_loh ( 'Process'           ) }
sub repository_contents_loh { my ( $self ) = @_ ; return $self->_as_loh ( 'RepositoryContent' ) }
sub repository_types_loh    { my ( $self ) = @_ ; return $self->_as_loh ( 'RepositoryType'    ) }
sub scm_types_loh           { my ( $self ) = @_ ; return $self->_as_loh ( 'ScmType'           ) }
sub stages_loh              { my ( $self ) = @_ ; return $self->_as_loh ( 'Stage'             ) }

sub product_to_component_lol {
  my ( $self, $scope ) = @_;
  
  my $h = {};

  {
    my $freeze_rs = $self->resultset ( 'Freeze' )->search (
      {
        'products_to_freeze.scope_id' => $scope,
      },
      {
        join      => 'products_to_freeze',
        '+select' => [ 'products_to_freeze.product_id' ],
        '+as'     => [                    'product_id' ],
        order_by  => {
          -asc => 'products_to_freeze.product_id',
        },
      },
    );
    
    while ( my $freeze_r = $freeze_rs->next ) {
      $h->{$freeze_r->get_column ( 'product_id' )}->{$freeze_r->component_id} = $freeze_r->component_version;
    }
  }
  
  my $lol = x_to_y_hash_as_lol ( $h, sub { return $_[0] } );

  if ( $lol ) {
    my $neo;
    
    my ( undef, @products ) = @{ shift ( @$lol ) };
    push @$neo, [ 'Component', 'Description', 'Type', map { { tag => 'span', id => $_, content => $_ } } @products ];

    for ( @$lol ) {
      my ( $name, @tail ) = @$_;

      my $component_r = $self->resultset ( 'Component' )->find ( $name );

      push @$neo, [ $name, $component_r->description, $component_r->type_id, @tail ];
    }
    
    $lol = $neo;
  }

  return $lol;
}

sub datasheets_loh {
  my ( $self, $c ) = @_;
  
  return $self->_as_loh ( 'Datasheet', $c );
}

sub repositories_loh {
  my ( $self, $c ) = @_;
  
  my $loh = $self->_as_loh ( 'Repository' );
  
  if ( my $content = $c->request->parameters->{content} ) {
    $loh = [ grep { $_->{content_id} eq $content } @$loh ]; 
  }
  
  my $do_not_strip;
  
  if ( my $user_r = $c->model ( 'App' )->user_r ( $c ) ) {
    if ( $user_r->bofh ) {
      $do_not_strip = 1;
    }
  }
  
  unless ( $do_not_strip ) {
    delete $_->{password} for @$loh;
  }
  
  return $loh;
}

sub specifications_loh {
  my ( $self, $c ) = @_;
  
  return $self->_as_loh ( 'Specification', $c );
}

sub sessions_loh {
  my ( $self, $tmp ) = @_;
  
  my $atime = sub { return DateTime->from_epoch ( epoch => (stat "$tmp/$_[0]")[8] )->iso8601               };
  my $delta = sub { return time_ago ( DateTime::Format::ISO8601->parse_datetime ( $_[0] ), DateTime->now ) };
  
  my $session;
  
  for ( $self->resultset ( 'Session' )->search ) {
    my $last = $_->last_access;
    
    $session->{$_->id} = { id => $_->id, last_access => $last, delta => $delta->( $last ), restful => $_->restful,
      busy        => $_->busy,
      user_id     => $_->user_id,
      user_name   => $_->user->name,
      last_path   => $_->last_path,
      last_method => $_->last_method,
      reapeable   => $_->user->fake ? 1 : $_->user->session_id ? ( $_->user->session_id ne $_->id ) : 0,
    };
  }
  
  for ( grep { defined && !$session->{$_} } map { m/ \/ ([0-9a-f]{40}) $/x ? $1 : undef } grep { -d } glob ( "$tmp/*" ) ) {
    my $last = $atime->( $_ );
    
    $session->{$_}     = { id => $_,     last_access => $last, delta => $delta->( $last ), restful => 1,
      reapeable => 1
    };
  }
  
  return [ map { $session->{$_} } sort { $session->{$b}->{last_access} cmp $session->{$a}->{last_access} } keys %$session ];
}

sub products_loh {
  my ( $self, $c ) = @_;
  
  if ( my $user_r = $self->user_r ( $c ) ) {
    my @loh;
    
    for my $product_h ( $self->resultset ( 'Product' )->search ( undef, { result_class => 'DBIx::Class::ResultClass::HashRefInflator' } ) ) {
      unless ( $user_r->bofh ) {
        my $owner  = $self->resultset ( 'UserToProduct' )->find ( $user_r->id, $product_h->{id}, 'owner'  );
        my $writer = $self->resultset ( 'UserToProduct' )->find ( $user_r->id, $product_h->{id}, 'writer' );

        if ( $product_h->{secure} ) {
          next unless $owner || $writer;
        }

        unless ( $owner ) {
          delete $product_h->{$_} for qw( integration_scm_password );
        }
      }
      
      push @loh, $product_h;
    }
    
    for ( @loh ) {
      $_->{owners}  = [ sort map { $_->user->name } $self->resultset ( 'UserToProduct' )->search ({ product_id => $_->{id}, authorization_id => 'owner'  }) ];
      $_->{writers} = [ sort map { $_->user->name } $self->resultset ( 'UserToProduct' )->search ({ product_id => $_->{id}, authorization_id => 'writer' }) ];
      
      next unless my $product_r = $self->resultset ( 'Product' )->find ( $_->{id} );
      
      $_->{integration_wiki_url}           = $product_r->integration_wiki_url;
      $_->{integration_wiki_url_as_html_a} = $product_r->integration_wiki_url_as_html_a;
    }

    return [ sort { ncmp ( $a->{id}, $b->{id} ) } @loh ];
  }
  
  return []; # FIXME die instead?
}

sub products_radiator_loh {
  my ( $self, $c, $rest ) = @_;
  
  my $loh = [];
  
  for my $product_r ( grep { not $_->dead } sort { ncmp ( $a->id, $b->id ) } $self->resultset ( 'Product' )->search ( {}, { order_by => 'id' } ) ) {
    my $h = {
      id                   => $product_r->id,
      pm_id                => $product_r->pm_id,
      stage_id             => $product_r->stage_id,
      specification_id     => $product_r->specification_id,
      specification_branch => $product_r->specification_branch,
      die_id               => $product_r->die_id,
      process_id           => $product_r->process_id,
    };

    if ( my $specification_id = $product_r->specification_id ) {
      if ( my $job = $self->resultset ( 'Job' )->search ( { specification_id => $specification_id, $self->not_bool ( 'dummy' ) }, { order_by => { -desc => 'when_completed' }, result_class => 'DBIx::Class::ResultClass::HashRefInflator' } )->first ) {
        delete $job->{main_log};
        
        $job->{when_completed} =~ s/(?<=\d)\s(?=\d)/T/;
        
        sanitized_hash ( $job );

        {
          $job->{when_completed_fuzzy} = $self->resultset ( 'Job' )->find ( $job->{id} )->when_completed_fuzzy;

          if ( $job->{when_completed} ) {
            $h->{last_ci}       = $job->{when_completed};
            $h->{last_ci_fuzzy} = $job->{when_completed_fuzzy};
          }
          elsif ( $job->{when_started} ) {
            $h->{last_ci}       = $job->{when_started};
          }

          $h->{status_id} = $job->{status_id}; # FIXME nuke when JS HoH scheme is understood
        }
        
        $h->{job} = $job;
      }
      
      if ( my $specification_r = $self->resultset ( 'Specification' )->find ( $specification_id ) ) {
        my @tags = map { $_->name } $specification_r->specification_tags;
        
        if ( @tags ) {
          $h->{specification_tags} = \@tags;
        }
      }
    }

    if ( my @product_to_freeze_rs = $product_r->products_to_freeze ) {
      for my $scope ( qw( specification integration dfs ) ) {
        my ( $product_to_freeze_r ) = ( sort { $b->last_update <=> $a->last_update } grep { $_->scope_id eq $scope } @product_to_freeze_rs )[0];
        if ( $product_to_freeze_r ) {
          $h->{last_pull}->{"${scope}"}       = $rest ? $product_to_freeze_r->last_update->iso8601 : $product_to_freeze_r->last_update;
          $h->{last_pull}->{"${scope}_fuzzy"} = $product_to_freeze_r->last_update_fuzzy;
        }
      }
    }

    push @$loh, sanitized_hash ( $h );
  }

  return [ sort { $b->{last_ci} cmp $a->{last_ci} } @$loh ];
}

sub products {
  my ( $self, $products ) = @_;
  
  # FIXME handle @$products option
  
  return sort { ncmp ( $a->{content}, $b->{content} ) } map { { content => $_->specification->id, value => $_->specification_id, id => $_->id } } $self->resultset ( 'Product' )->search;
}

sub specified_products {
  my ( $self, $option ) = @_;
  
  my $product;
  
  for ( grep { $_->specification_id } $self->resultset ( 'Product' )->search ) {
    if ( $option->{has_devices} ) {
      my @devices = $_->devices;
      
      unless ( @devices ) {
        next;
      }
    }
    
    $product->{$_->id} = { id => $_->id, specification_id => $_->specification_id, die_id => $_->die_id };
  }
  
  return map { $_->[1] } sort { ncmp ( $a->[0], $b->[0] ) } map { [ $_, $product->{$_} ] } keys %$product;
}

sub integrated_products {
  my ( $self ) = @_;
  
  my $product;
  
  for ( $self->resultset ( 'ProductToFreeze' )->search ({ scope_id => 'integration' }) ) {
    my $id = $_->product_id;
    
    next if $product->{$id};
    
    $product->{$id} = { id => $id, specification_id => $_->product->specification_id, die_id => $_->product->die_id };
  }
  
  for ( $self->resultset ( 'Product' )->search ) {
    my $id = $_->id;
    
    next if $product->{$id};
    next unless $_->integration_scm_url;
    
    $product->{$id} = { id => $id, specification_id => $_->specification_id, die_id => $_->die_id };
  }
  
  return map { $_->[1] } sort { ncmp ( $a->[0], $b->[0] ) } map { [ $_, $product->{$_} ] } keys %$product;
}

sub datasheeted_products {
  my ( $self ) = @_;
  
  my $product;
    
  my $freeze_rs = $self->resultset ( 'Freeze' )->search (
    {
      'component.type_id'           => 'datasheet',
      'products_to_freeze.scope_id' => 'specification',
    },
    {
      join      => [ { 'products_to_freeze' => 'product' }, 'component' ],
      '+select' => [ 'products_to_freeze.product_id', 'product.specification_id' ],
      '+as'     => [                    'product_id',         'specification_id' ],
      order_by  => [
        { -asc => 'products_to_freeze.product_id' },
        { -asc => 'component.id'                  },
        { -asc => 'products_to_freeze.scope_id'   },
      ],
    },
  );

  while ( my $freeze_r = $freeze_rs->next ) {
    my $id = $freeze_r->get_column ( 'product_id' );
    
    next if $product->{$id};
    
    $product->{$id} = { id => $id, specification_id => $freeze_r->get_column ( 'specification_id' ) };
  }

  return map { $_->[1] } sort { ncmp ( $a->[0], $b->[0] ) } map { [ $_, $product->{$_} ] } keys %$product;
}

sub specification_branches {
  my ( $self, $option ) = @_;
  
  if ( my $specification_r = $self->resultset ( 'Specification' )->find ( $option->{specification_id} ) ) {
    return nsort map { $_->name } $specification_r->specification_branches;
  }
  
  return;
}

sub specification_tags {
  my ( $self, $option ) = @_;
  
  if ( my $specification_r = $self->resultset ( 'Specification' )->find ( $option->{specification_id} ) ) {
    return nsort map { $_->name } $specification_r->specification_tags;
  }
  
  return;
}

sub specification_datasheets {
  my ( $self, $option ) = @_;
  
  my $c = $option->{c};
  
  my $freeze_rs = $self->resultset ( 'Freeze' )->search (
    {
      'component.type_id'           => 'datasheet',
      'products_to_freeze.scope_id' => 'specification',
      'product.specification_id'    => $option->{specification_id},
    },
    {
      join      => [ { 'products_to_freeze' => 'product' }, 'component' ],
      '+select' => [ 'products_to_freeze.product_id', 'product.specification_id', 'component.description', 'component.url' ],
      '+as'     => [                    'product_id',         'specification_id', 'component_description', 'component_url' ],
      order_by  => [
        { -asc => 'products_to_freeze.product_id' },
        { -asc => 'component.id'                  },
        { -asc => 'products_to_freeze.scope_id'   },
      ],
    },
  );

  my @datasheets;

  while ( my $freeze_r = $freeze_rs->next ) {
    ( my $dstp_id = $freeze_r->component_id ) =~ s/^[^_]+_//;
    
    my ( $component_id, $component_version ) = ( $freeze_r->component_id, $freeze_r->component_version );
    
    my $h = {
      id          => $component_id,
      version     => $component_version,
      description => $freeze_r->get_column ( 'component_description' ),
      url         => $freeze_r->get_column ( 'component_url'         ),
      issues      => $c->model ( 'DSTP' )->issues ( $dstp_id, $freeze_r->component_version ),
    };
    
    if ( my $datasheet_r = $self->resultset ( 'Datasheet' )->find ( $component_id ) ) {
      if ( $datasheet_r->ahead ) {
        $h->{svn_ahead} = 1;
        $h->{svn_last}  = $datasheet_r->last_tag;
      }
    }
    
    push @datasheets, $h;
  }
  
  return sort { $a->{id} <=> $b->{id} } @datasheets;
}

sub product_datasheets {
  my ( $self, $option ) = @_;
  
  my $c = $option->{c};
  
  my $freeze_rs = $self->resultset ( 'Freeze' )->search (
    {
      'component.type_id'           => 'datasheet',
      'products_to_freeze.scope_id' => 'specification',
      'product.id'                  => $option->{product_id},
    },
    {
      join      => [ { 'products_to_freeze' => 'product' }, 'component' ],
      '+select' => [ 'products_to_freeze.product_id', 'product.specification_id', 'component.description', 'component.url', 'component.tracker_id' ],
      '+as'     => [                    'product_id',         'specification_id', 'component_description', 'component_url',           'tracker_id' ],
      order_by  => [
        { -asc => 'products_to_freeze.product_id' },
        { -asc => 'component.id'                  },
        { -asc => 'products_to_freeze.scope_id'   },
      ],
    },
  );

  my @datasheets;

  while ( my $freeze_r = $freeze_rs->next ) {
    my $datasheet_id = $freeze_r->component_id;
    
    #
    # Core.
    #
    
    my $h = {
      id          => $datasheet_id,
      version     => $freeze_r->component_version,
      description => $freeze_r->get_column ( 'component_description' ),
      url         => $freeze_r->get_column ( 'component_url'         ),
    };
    
    delete $h->{$_} for grep { ! defined $h->{$_} } keys %$h;
    
    #
    # Issues.
    #
    
    if ( $c && $option->{issues} ) {
      if ( my $tracker_id = $freeze_r->get_column ( 'tracker_id' ) ) {
        if ( my $tracker_r = $self->resultset ( 'Tracker' )->find ( $tracker_id ) ) {
          if ( $tracker_r->api_id eq 'BZC' ) {
            if ( my $issues = $c->model ( 'BZC' )->issues ( $h->{id}, $h->{version}, $c, $tracker_r ) ) {
              $h->{issues} = $issues;
            }
          }
        }
      }
      else {
        ( my $dstp_id = $freeze_r->component_id ) =~ s/^[^_]+_//;

        $h->{issues} = $c->model ( 'DSTP' )->issues ( $dstp_id, $freeze_r->component_version );
      }
    }
    
    #
    # Ahead.
    #
    
    if ( my $datasheet_r = $self->resultset ( 'Datasheet' )->find ( $datasheet_id ) ) {
      $h->{svn_last} = $datasheet_r->last_tag;
      
      if ( $datasheet_r->ahead ) {
        $h->{svn_ahead} = 1;
      }
      
      my $impacts = ci_svn_tail ({
        xls_revision => $h->{version},
        svn_exists   => $self->resultset ( 'DatasheetTag' )->find ( $h->{version}, $datasheet_id ),
        svn_latest   => $h->{svn_last},
        svn_ahead    => $h->{svn_ahead},
      }) eq 'trunk';
      
      if ( $impacts ) {
        $h->{impacts} = 1;
      }
    }
    
    push @datasheets, $h;
  }
  
  return sort { ncmp ( $a->{id}, $b->{id} ) } @datasheets;
}

sub product_devices {
  my ( $self, $option ) = @_;
  
  my @devices;
  
  for my $p2d_r ( $self->resultset ( 'ProductToDevice' )->search ({ product_id => $option->{product_id} }) ) {
    my $device_r = $p2d_r->device;
    
    push @devices, { id => $device_r->id, description => $device_r->description, url => $device_r->url };
  }
  
  return sort { ncmp ( $a->{id}, $b->{id} ) } @devices;
}

sub _write_whatever {
  my ( $worksheet_id, $row, $column, $content, $format ) = @_;
  
  if ( ref $content eq 'HASH' ) {
    if ( $content->{href} ) {
      $worksheet_id->write_url    ( $row, $column, $content->{href}, $content->{content}, $format );
    }
    else {
      $worksheet_id->write_string ( $row, $column,                   $content->{content}, $format );
    }
  }
  else {
      $worksheet_id->write_string ( $row, $column,                   $content || '-',     $format );
  }
}

sub _write_string {
  my ( $worksheet_id, $row, $column, $content, $format ) = @_;
  
  $worksheet_id->write_string ( $row, $column, $content || '-', $format );
}

sub product_components_loh_to_xls_file {
  my ( $self, $option ) = @_;
  
  my ( $product, $loh, $file, $author ) = map { $option->{$_} } qw( product loh file author );
  
  if ( $loh ) {
    if ( my $workbook_id = Spreadsheet::WriteExcel->new ( $file ) ) {
      my $version_string = iSOCK->version_string . " GUI to " . SOCK->version_string . " CLI";

      $workbook_id->set_properties (
        title    => "$product Components",
        author   => $author || 'iSOCK',
        comments => "automagically generated by $version_string",
      );

      my ( $worksheet_id, $format ) = add_work_sheet ({ workbook_id => $workbook_id, worksheet_name => 'Components' });
      
      my @widths = ( 24,           12,              24,          12,             12,     12,              48           );
      my @heads  = ( 'Spec. Name', 'Spec. Version', 'Int. Name', 'Int. Version', 'Type', 'Last Version', 'Description' );

      $worksheet_id->set_column   ( $_, $_, $widths[$_]                 ) for 0 .. @widths - 1;
      $worksheet_id->write_string ( 0,  $_, $heads[$_], $format->{head} ) for 0 .. @heads  - 1;
      
      {
        my $row = 1;

        tie my $class, 'Tie::FlipFlop', qw( odd even );

        for my $component_h ( sort { $a->{component_type} cmp $b->{component_type} } @$loh ) {
          my $cell_format = $class eq 'odd' ? $format->{odd} : $format->{even};

          _write_string   ( $worksheet_id => $row, 0, $component_h->{specification_name},       $cell_format );
          _write_whatever ( $worksheet_id => $row, 1, $component_h->{specification_version},    $cell_format );
          _write_string   ( $worksheet_id => $row, 2, $component_h->{integration_name},         $cell_format );
          _write_whatever ( $worksheet_id => $row, 3, $component_h->{integration_version},      $cell_format );
          _write_string   ( $worksheet_id => $row, 4, $component_h->{component_type},           $cell_format );
          _write_whatever ( $worksheet_id => $row, 5, $component_h->{bug_tracker_last_version}, $cell_format );
          _write_string   ( $worksheet_id => $row, 6, $component_h->{component_description},    $cell_format );

          $row++;
        }
        
        $worksheet_id->autofilter ( 0, 4, scalar ( @$loh ) - 1, 4 );
      }

      $workbook_id->close;
    }
  }
  
  return;
}

sub product_components_loh {
  my ( $self, $product ) = @_;
  
  my @loh;
  
  #
  # Integration.
  #
  
  {
    my $freeze_rs = $self->resultset ( 'Freeze' )->search (
      {
        'products_to_freeze.product_id' => $product,
        'products_to_freeze.scope_id'   => 'integration',
      },
      {
        join      => 'products_to_freeze',
        '+select' => [ 'products_to_freeze.last_update' ],
        '+as'     => [                    'last_update' ],
        order_by  => {
          -asc => 'products_to_freeze.product_id',
        },
      },
    );
    
    my @columns = qw( integration_name integration_version integration_type integration_changed integration_description );

    while ( my $freeze_r = $freeze_rs->next ) {
      my @values = map { $freeze_r->$_ } qw( component_id component_version component_type );
      push @values, $freeze_r->get_column ( 'last_update' );
      push @values, $freeze_r->component->description;

      push @loh, { mesh @columns, @values };
    }
  }
  
  #
  # Specification.
  #
  
  {
    my $freeze_rs = $self->resultset ( 'Freeze' )->search (
      {
        'products_to_freeze.product_id' => $product,
        'products_to_freeze.scope_id'   => 'specification',
      },
      {
        join      => 'products_to_freeze',
        '+select' => [ qw( products_to_freeze.product_id products_to_freeze.last_update ) ],
        '+as'     => [ qw(                    product_id                    last_update ) ],
        order_by  => {
          -asc => 'products_to_freeze.product_id',
        },
      },
    );

    my @columns = qw( specification_name specification_version specification_type specification_changed specification_description );

    while ( my $freeze_r = $freeze_rs->next ) {
      my $found;

      for ( grep { $_->{integration_name} eq $freeze_r->component_id } grep { $_->{integration_name} } @loh ) {
        $_->{specification_name}    = $freeze_r->component_id;
        $_->{specification_type}    = $freeze_r->component_type;
        $_->{specification_version} = $freeze_r->component_version;
        
        $found = 1;
      }

      unless ( $found ) {
        my @values = map { $freeze_r->$_ } qw( component_id component_version component_type );
        push @values, $freeze_r->get_column ( 'last_update' );
        push @values, $freeze_r->component->description;

        push @loh, { mesh @columns, @values };
      }
    }
  }
  
  {
    my $freeze_rs = $self->resultset ( 'Freeze' )->search (
      {
        'products_to_freeze.product_id' => $product,
        'products_to_freeze.scope_id'   => 'dfs',
      },
      {
        join      => 'products_to_freeze',
        '+select' => [ qw( products_to_freeze.product_id products_to_freeze.last_update ) ],
        '+as'     => [ qw(                    product_id                    last_update ) ],
        order_by  => {
          -asc => 'products_to_freeze.product_id',
        },
      },
    );

    my @columns = qw( dfs_name dfs_version dfs_type dfs_changed dfs_description );

    while ( my $freeze_r = $freeze_rs->next ) {
      my $found;

      for ( grep { $_->{integration_name} eq $freeze_r->component_id } grep { $_->{integration_name} } @loh ) {
        $_->{dfs_name}    = $freeze_r->component_id;
        $_->{dfs_type}    = $freeze_r->component_type;
        $_->{dfs_version} = $freeze_r->component_version;
        
        $found = 1;
      }

      unless ( $found ) {
        my @values = map { $freeze_r->$_ } qw( component_id component_version component_type );
        push @values, $freeze_r->get_column ( 'last_update' );
        push @values, $freeze_r->component->description;

        push @loh, { mesh @columns, @values };
      }
    }
  }
  
  for ( @loh ) {
    if ( $_->{specification_type} && $_->{integration_type} ) {
      $_->{component_type}        = $_->{specification_type} eq $_->{integration_type}               ? $_->{specification_type}        : "$_->{specification_type} (specification) vs $_->{integration_type} (integration)";
    }
    else {
      $_->{component_type}        = $_->{specification_type} || $_->{integration_type} || $_->{dfs_type};
    }
    
    if ( $_->{specification_description} && $_->{integration_description} ) {
      $_->{component_description} = $_->{specification_description} eq $_->{integration_description} ? $_->{specification_description} : "$_->{specification_description} (specification) vs $_->{integration_description} (integration)";
    }
    else {
      $_->{component_description} = $_->{specification_description} || $_->{integration_description} || $_->{dfs_description};
    }
  }
  
  return [ sort { ( $a->{integration_name} || $a->{specification_name} || $a->{dfs_name} ) cmp ( $b->{integration_name} || $b->{specification_name} || $b->{dfs_name} ) } @loh ];
}

sub feed_component_lineup {
  my ( $self, $option ) = @_;
  
  return unless my $data = $option->{data};
  
  my $id    = $data->{product_id};
  my $scope = $data->{product_scope};
  
  return unless $id && $scope;
  
  my $dummy = $option->{dummy};

  #
  # Core.
  #

  my @actions;
    
  my $now = DateTime->now;
  
  my $done;
  
  for my $component_h ( @{$data->{components}} ) {
    next unless my $component_id    = $component_h->{name}; # FIXME handle ->{id} instead?
    
    my $component_version           = $component_h->{version};
    my $component_version_as_stored = iSOCK::Schema::Tricks->version_to_storage ( $component_version );
    my $freeze_id_sql               = "( SELECT id FROM freeze WHERE component_id = '$component_id' AND component_version = '$component_version_as_stored' )";
    my $freeze_tuple                = "( '$component_id', '$component_version_as_stored' )";

    next if exists $done->{ $component_id, $component_version_as_stored };
    
    my $component_r;

    {
      my $component_type = $component_h->{type};

      try {
        unless ( $component_r = $self->resultset ( 'Component' )->find ( $component_id ) ) {
          $component_r = $self->resultset ( 'Component' )->create ({ id => $component_id, type_id => $component_type });
          
          push @actions, {
            action      => 'create',
            table       => 'component',
            row         => { component_id => $component_id, component_type => $component_type },
            description => "create ( '$component_id', '$component_type' ) component",
          };
        }
        
        $component_r->update ({ description => $component_h->{description} }) if $component_h->{description};
        $component_r->update ({ url         => $component_h->{url}         }) if $component_h->{url};
        $component_r->update ({ tracker_id  => $component_h->{tracker}     }) if $component_h->{tracker};
      }
      catch {
        push @actions, { error => "could not find or create { id => $component_id, type_id => $component_type } component" };
      };
    }

    if ( $component_r ) {
      #
      # Update or create freeze.
      #

      my $freeze_r = $self->resultset ( 'Freeze' )->search ({ component_id => $component_id, component_version => $component_version_as_stored })->single;

      unless ( $freeze_r ) {
        push @actions, {
          action      => 'create',
          table       => 'freeze',
          row         => { component_id => $component_id, component_version => $component_version_as_stored },
          description => "create $freeze_tuple freeze",
        };

        unless ( $dummy ) {
          try {
            $freeze_r = $self->resultset ( 'Freeze' )->create ({ component_id => $component_id, component_version => $component_version });
          }
          catch {
            die $_;
          };
        }
      }

      #
      # Create product-to-freeze, if applicable.
      #

      my $freeze_rj = $self->resultset ( 'Freeze' )->search (
        {
          component_id                    => $component_id,
          component_version               => $component_version_as_stored,
          'products_to_freeze.product_id' => $id,
          'products_to_freeze.scope_id'   => $scope,
        },
        {
          join                            => 'products_to_freeze',
        },
      )->single;

      unless ( $freeze_rj ) {
        push @actions, {
          action      => 'create',
          table       => 'product_to_freeze',
          row         => { product_id => $id, freeze_id => $freeze_id_sql, scope_id => $scope },
          description => "link '$id' product with $freeze_tuple freeze",
        };

        $self->resultset ( 'ProductToFreeze' )->create ({ product_id => $id, freeze_id => $freeze_r->id, scope_id => $scope, last_update => $now }) unless $dummy;
      }
    }
    
    $done->{ $component_id, $component_version_as_stored } = 1;
  }

  #
  # Collect garbage.
  #

  for my $p2f_r ( $self->resultset ( 'ProductToFreeze' )->search ({ product_id => $id, scope_id => $scope }) ) {
    my $freeze_r = $p2f_r->freeze;

    next if grep { $_->{name} eq $freeze_r->component_id } @{$data->{components}};

    push @actions, {
      action      => 'delete',
      table       => 'product_to_freeze',
      row         => {
        product_id => $id,
        freeze_id  => "( SELECT id FROM freeze WHERE component_id = '" . $freeze_r->component_id . "' AND component_version = '" . $freeze_r->component_version . "' )",
        scope_id   => $scope,
      },
      description => "unlink '$id' product from ( '" . $freeze_r->component_id . "', '" . $freeze_r->component_version . "' ) freeze",
    };

    $p2f_r->delete unless $dummy;
  }
  
  for ( @{$data->{components}} ) {
    my $freeze_rs = $self->resultset ( 'Freeze' )->search (
      {
        component_id                    => $_->{name},
        'products_to_freeze.product_id' => $id,
        'products_to_freeze.scope_id'   => $scope,
      },
      {
        join                            => 'products_to_freeze',
        '+select'                       => [ 'products_to_freeze.product_id' ],
        '+as'                           => [                    'product_id' ],
      },
    );
    
    my $component_version_as_stored = iSOCK::Schema::Tricks->version_to_storage ( $_->{version} );
    
    while ( my $freeze_r = $freeze_rs->next ) {
      next if $freeze_r->component_version eq $component_version_as_stored;
            
      push @actions, {
        action => 'delete',
        table  => 'product_to_freeze',
        row    => {
          product_id => $id,
          freeze_id  => "( SELECT id FROM freeze WHERE component_id='" . $freeze_r->component_id . "' AND component_version='" . $freeze_r->component_version . "' )",
          scope_id   => $scope,
        },
        description => "unlink '$id' product from ( '" . $freeze_r->component_id . "', '" . $freeze_r->component_version . "' ) freeze",
      };

      $self->resultset ( 'ProductToFreeze' )->search ({ product_id => $freeze_r->get_column ( 'product_id' ), freeze_id => $freeze_r->id, scope_id => $scope })->single->delete unless $dummy;
    }
  }
  
  #
  # Update product, if applicable.
  #
  
  unless ( $dummy ) {
    if ( my $product_r = $self->resultset ( 'Product' )->find ( $id ) ) {
      $product_r->set_column ( integration_config_chip_id       => $data->{integration_config_chip_id}       ) if $data->{integration_config_chip_id};
      $product_r->set_column ( integration_config_technology_id => $data->{integration_config_technology_id} ) if $data->{integration_config_technology_id};

      $product_r->update;
    }
  }
  
  #
  # Devices.
  #
  
  if ( $data->{devices} ) {
    if ( my @devices = @{$data->{devices}} ) {
      my $to_delete;
         $to_delete->{$_->device_id} = 1 for $self->resultset ( 'ProductToDevice' )->search ({ product_id => $id });

      for my $device_id ( @{$data->{devices}} ) {
        next unless supported_device_name ( $device_id );

        delete $to_delete->{$device_id};

        unless ( $self->resultset ( 'Device' )->find ( $device_id ) ) {
          push @actions, {
            action      => 'create',
            table       => 'device',
            row         => { id => $device_id },
            description => "create $device_id device",
          };

          unless ( $dummy ) {
            $self->resultset ( 'Device' )->create ({ id => $device_id });
          }
        }

        unless ( $self->resultset ( 'ProductToDevice' )->find ( $id, $device_id ) ) {
          push @actions, {
            action      => 'create',
            table       => 'product_to_device',
            row         => { product_id => $id, device_id => $device_id },
            description => "link $id product with $device_id device",
          };

          unless ( $dummy ) {
            $self->resultset ( 'ProductToDevice' )->create ({ product_id => $id, device_id => $device_id });
          }
        }
      }

      for my $device_id ( nsort keys %$to_delete ) {
        push @actions, {
          action      => 'delete',
          table       => 'product_to_device',
          row         => { product_id => $id, device_id => $device_id },
          description => "unlink $id product from $device_id device",
        };

        unless ( $dummy ) {
          $self->resultset ( 'ProductToDevice' )->find ( $id, $device_id )->delete;
        }
      }
    }
  }
  
  #
  # Lines.
  #
  
  if ( my $line_to_devices = $data->{line_to_devices} ) {
    for my $line_id ( nsort keys %$line_to_devices ) {
      unless ( $self->resultset ( 'Line' )->find ( $line_id ) ) {
        push @actions, {
          action      => 'create',
          table       => 'line',
          row         => { id => $line_id },
          description => "create $line_id line",
        };

        unless ( $dummy ) {
          $self->resultset ( 'Line' )->create ({ id => $line_id });
        }
      }
      
      for my $device_id ( @{$line_to_devices->{$line_id}->{devices}} ) {
        if ( my $device_r = $self->resultset ( 'Device' )->find ( $device_id ) ) {
          if ( !$device_r->line_id || $device_r->line_id ne $line_id ) {
            push @actions, {
              action      => 'update',
              table       => 'device',
              row         => { line_id => $line_id },
              description => "update $device_id device with $line_id line",
            };

            unless ( $dummy ) {
              $device_r->update ({ line_id => $line_id });
            }
          }
        }
        else {
          push @actions, {
            action      => 'create',
            table       => 'device',
            row         => { id => $device_id, line_id => $line_id },
            description => "create $device_id device",
          };

          unless ( $dummy ) {
            $self->resultset ( 'Device' )->create ({ id => $device_id, line_id => $line_id });
          }
        }
      }
    }
  }
  
  return \@actions;
}

sub product_history_loh {
  my ( $self, $product ) = @_;

  my $join    = 'freeze';
  my @columns = qw( component_id component_version );

  my $p2f_rs = $self->resultset ( 'ProductToFreeze' )->search ( { product_id => $product }, {
    join      => { $join => 'component' },
    '+select' => [ 'component.type_id', map { "$join.$_" } @columns ],
    '+as'     => [ 'component_type_id',                    @columns ],
    order_by  => {
      -desc => 'last_update',
    },
  } );

  return [ map { {
    last_update       => $_->last_update->iso8601,
    scope_id          => $_->scope_id,
    product_id        => $_->product_id,
    component_id      => $_->get_column ( 'component_id'      ),
    component_version => $_->get_column ( 'component_version' ),
    component_type    => $_->get_column ( 'component_type_id' ),
  } } $p2f_rs->all ];
}

#
# Product CRUD.
#

sub is_product_id_valid {
  my ( $self, $id ) = @_;
  
  return $id =~ /^ [A-Za-z0-9]+ - [A-Z] $/x;
}

sub create_product {
  my ( $self, $option ) = @_;
  
  my ( $id ) = map { $option->{$_} } qw( id );
  
  die 'Empty ID!'     unless $id;
  die 'Invalid ID!'   unless $self->is_product_id_valid   ( $id   );
  
  die 'Duplicate ID!' if     $self->resultset ( 'Product' )->find ( $id );
  
  try {
    return $self->resultset ( 'Product' )->create ( $option );
  }
  catch {
    die $_;
  };
}

#
# User (dual authentication scheme).
#

sub user_r {
  my ( $self, $c ) = @_;
  
  if ( $c ) {
    if ( my $hash = $c->request->parameters->{hash} ) {
      return $c->model ( 'App::User' )->search ({ rest_hash => $hash })->first;
    }
    elsif ( my $user = $c->user ) {
      my $id;
      
      if ( ref $user eq 'Catalyst::Authentication::Store::DBIx::Class::User' ) {
        $id = $user->get ( 'id' );
      }
      elsif ( ref $user eq 'Catalyst::Authentication::Store::LDAP::User' ) {
        $id = $user->id;
      }
      else {
        $c->log->error ( "[Model::App::user_r] Could not get any user ID!" );
        
        return;
      }
      
      return $self->resultset ( 'User' )->find ( $id );
    }
    else {
      $c->log->error ( "[Model::App::user_r] Undefined \$c->user () result!" );
    }
  }
  
  return;
}

sub user_id {
  my ( $self, $c ) = @_;
  
  if ( my $user_r = $self->user_r ( $c ) ) {
    return $user_r->id;
  }
  
  return;
}

sub info_string {
  my ( $self, $c ) = @_;
  
  if ( my $user_r = $self->user_r ( $c ) ) {
    return "U: " . $user_r->id . " ; A: " . $c->request->address . " ; S: " . $c->sessionid;
  }
  
  return;
}

#
# Session.
#

sub alter_session {
  my ( $self, $option ) = @_;
  
  my $c          = $option->{c};
  my $session_id = $option->{session_id} || $c->sessionid;
  
  my $user_r = $c->stash->{user_id} ?
    $self->resultset ( 'User' )->find ( $c->stash->{user_id} )
  : ( $option->{user_r} || $self->user_r ( $c ) )
  ;

  if ( $user_r && $session_id ) {
    if ( my $session_r = $self->resultset ( 'Session' )->find ( $session_id ) ) {
      my $succeeded = $session_r->update ({
        busy        => ( $option->{busy}        || 0             ),
        restful     => ( $option->{restful}     || 0             ),
        last_access => ( $option->{last_access} || DateTime->now ),
        last_path   => $c->request->path,
        last_method => $c->request->method,
      });
      
      if ( $succeeded ) {
        $c->log->info  ( "[Model::App::alter_session] Updated session $session_id with { last_path => " . $c->request->path . " } in DB." );
      }
      else {
        $c->log->error ( "[Model::App::alter_session] Could not update $session_id session in DB!" );
      }
    }
    else {
      my $succeeded = $self->resultset ( 'Session' )->update_or_create ({
        id          => $session_id,
        user_id     => $user_r->id,
        busy        => ( $option->{busy}        || 0             ),
        restful     => ( $option->{restful}     || 0             ),
        last_access => ( $option->{last_access} || DateTime->now ),
        last_path   => $c->request->path,
        last_method => $c->request->method,
      });

      if ( $succeeded ) {
        $c->log->info  ( "[Model::App::alter_session] Created session $session_id with { user_id => " . $user_r->id . " } in DB." );

        return 1;
      }
      else {
        $c->log->error ( "[Model::App::alter_session] Could not create $session_id session in DB!" );
      }
    }
  }
  elsif ( $session_id ) {
    $c->log->warn ( "[Model::App::alter_session] Did nothing (no user)." );
  }
  elsif ( $user_r ) {
    $c->log->warn ( "[Model::App::alter_session] Did nothing (no session)." );
  }
  else {
    $c->log->warn ( "[Model::App::alter_session] Did nothing (no user or no session)." );
  }
  
  return 0;
}

sub can_shutdown {
  my ( $self ) = @_;
  
  return scalar ( $self->resultset ( 'Session' )->search ({ busy => 1 })->all ) ? 0 : 1;
}

#
# Process CRUD.
#

sub is_process_id_valid {
  my ( $self, $id ) = @_;
  
  return $id =~ /^ at [0-9]{5} $/x;
}

sub create_process {
  my ( $self, $option ) = @_;
  
  my ( $id, $name ) = map { $option->{$_} } qw( id name );
  
  die 'Empty ID!'     unless $id;
  die 'Invalid ID!'   unless $self->is_process_id_valid ( $id );
  
  die 'Duplicate ID!' if     $self->resultset ( 'Process' )->find ( $id );
  
  try {
    return $self->resultset ( 'Process' )->create ( $option );
  }
  catch {
    die $_;
  };
}

#
# Copied from SOCK::BOT package.
#

=head2 x_to_y_hash_as_lol

  $lol = x_to_y_hash_as_lol ( $hash [, sub {} ] )

Translates an X to Y relationship (i.e., exists $hash->{$x}->{$y}) into a LoL.

=cut

sub x_to_y_hash_as_lol {
  my ( $hash, $sub ) = @_;
  
  $sub ||= sub { return $_[0] ? 'X' : '-' };
  
  my @xs = nsort keys %$hash;
  my %y;
     $y{$_}++ for map { keys %{$hash->{$_}} } @xs;
  my @ys = nsort keys %y;
  
  my $lol = [ [ '~', @xs ] ];
  
  for my $y ( @ys ) {
    push @$lol, [ $y, map { $sub->( $hash->{$_}->{$y} ) } @xs ];
  }
  
  $lol;
}

#
# Preference X Authorization.
#

sub lurkers_mails {
  my ( $self, $option ) = @_;
  
  return $self->datasheet_mails     ( $option ) if $option->{datasheet_id};
  return $self->specification_mails ( $option ) if $option->{specification_id};
  
  return;
}

#
# Preference X Authorization 4 Datasheet.
#

sub datasheet_mail_user_id_hash {
  my ( $self, $option ) = @_;
  
  my $id = {};
  
  if ( my $datasheet_r = $self->resultset ( 'Datasheet' )->find ( $option->{datasheet_id} ) ) {
    my $secure = $datasheet_r->secure;
    
    unless ( $secure ) {
      for ( grep { ! $_->user->fake } $self->resultset ( 'UserToPreference' )->search ({ preference_id => 'any_datasheet_event_mail', value => 'yes' }) ) {
        $id->{$_->user_id} = $_->user->mail_address;
      }
    }

    for ( grep { ! $_->user->fake } $self->resultset ( 'UserToDatasheet' )->search ({
      datasheet_id     => $option->{datasheet_id},
      authorization_id => [ { '=' => 'owner' }, { '=' => 'writer' }, { '=' => 'follower' } ],
    }) ) {
      $id->{$_->user_id} = $_->user->mail_address; 
    }

    unless ( $secure ) {
      for ( grep { ! $_->user->fake } $self->resultset ( 'UserToRole' )->search ({ role_id => [ { '=' => 'owns_all_datasheets' }, { '=' => 'writes_all_datasheets' } ] }) ) {
        $id->{$_->user_id} = $_->user->mail_address; 
      }
    }
  }
      
  return $id;
}

sub datasheet_mail_user_ids {
  my ( $self, $option ) = @_;
  
  my $id = $self->datasheet_mail_user_id_hash ( $option );
  
  return sort keys %$id;
}

sub datasheet_mails {
  my ( $self, $option ) = @_;
  
  my $id = $self->datasheet_mail_user_id_hash ( $option );
  
  return sort map { $id->{$_} } keys %$id;
}

#
# Preference X Authorization 4 Specification.
#

sub specification_mail_user_id_hash {
  my ( $self, $option ) = @_;
  
  my $id = {};
  
  if ( my $specification_r = $self->resultset ( 'Specification' )->find ( $option->{specification_id} ) ) {
    my $secure = $specification_r->secure;
    
    unless ( $secure ) {
      for ( grep { ! $_->user->fake } $self->resultset ( 'UserToPreference' )->search ({ preference_id => 'any_specification_event_mail', value => 'yes' }) ) {
        next if $_->user->fake;
        $id->{$_->user_id} = $_->user->mail_address;
      }
    }

    for ( grep { ! $_->user->fake } $self->resultset ( 'UserToSpecification' )->search ({
      specification_id => $option->{specification_id},
      authorization_id => [ { '=' => 'owner' }, { '=' => 'writer' }, { '=' => 'follower' } ],
    }) ) {
      $id->{$_->user_id} = $_->user->mail_address; 
    }

    unless ( $secure ) {
      for ( grep { ! $_->user->fake } $self->resultset ( 'UserToRole' )->search ({ role_id => [ { '=' => 'owns_all_specifications' }, { '=' => 'writes_all_specifications' } ] }) ) {
        $id->{$_->user_id} = $_->user->mail_address; 
      }
    }
  }
      
  return $id;
}

sub specification_mail_user_ids {
  my ( $self, $option ) = @_;
  
  my $id = $self->specification_mail_user_id_hash ( $option );
  
  return sort keys %$id;
}

sub specification_mails {
  my ( $self, $option ) = @_;
  
  my $id = $self->specification_mail_user_id_hash ( $option );
  
  return sort map { $id->{$_} } keys %$id;
}

#
# Datasheet.
#

=head2 whatever_hoh

  $hoh = $self->whatever_hoh ({ c => $c, whatever => datasheet|specification, whatever_r => $whatever_r })

Returns a HoH like:

  {
    id             => ,
    repository_id  => ,
    repository_url => ,
    
    users => [
      {
        id   => ,
        name => ,
        
        owner    | writer     | follower    => 1,
        owns_all | writes_all | follows_all => 1,
      },
    ],
    
    owners|writers|followers => [
      {
        id   => ,
        name => ,
      },
    ],
  }

=cut

sub whatever_hoh {
  my ( $self, $option ) = @_;
  
  my $c          = $option->{c};
  my $whatever   = $option->{whatever};
  my $whatever_r = $option->{whatever_r};
    
  
  my $entity     = {
    id             => $whatever_r->id,
    repository_id  => $whatever_r->repository_id,
    repository_url => $whatever_r->repository->url,
  };
  
  if ( $whatever eq 'specification' ) {
    $entity->{repository_integration_url} = "$entity->{repository_url}/$entity->{id}/trunk/integration";
    
    my $products = $whatever_r->products_loh ();
    
    if ( @$products ) {
      $entity->{products} = $products;
    }
  }

  my $whateverz         = $whatever . 's';
  my $user_to_whateverz = "user_to_$whateverz";
  
  for ( $whatever_r->$user_to_whateverz ) {
    push @{$entity->{$_->authorization_id . 's'}}, { id => $_->user_id, name => $_->user->name, fake => $_->user->fake };
  }

  my @authorizations = grep { $_ !~ /^(id|products|repository_id|repository_url|repository_integration_url)$/ } keys %$entity;

  for ( @authorizations ) {
    $entity->{$_} = [ map { $_->[1] } sort { $a->[0] cmp $b->[0] } map { [ $_->{name}, $_ ] } @{$entity->{$_}} ];
  }

  {
    my $user;

    for my $authorization ( @authorizations ) {
      for ( @{$entity->{$authorization}} ) {
        $user->{$_->{name}}->{$authorization} = 1;
        $user->{$_->{name}}->{id}             = $_->{id};
      }
    }

    my @users;

    for my $name ( sort keys %$user ) {
      my $user_h = $user->{$name};

      my $h = { id => $user_h->{id}, name => $name };

      $h->{$_} = 1 for grep { $user_h->{"${_}s"} } qw( owner writer follower );

      $h->{owns_all}    = 1 if $c->model ( 'App::UserToRole'       )->find ( $user_h->{id}, "owns_all_$whateverz"        );
      $h->{writes_all}  = 1 if $c->model ( 'App::UserToRole'       )->find ( $user_h->{id}, "writes_all_$whateverz"      );
      $h->{follows_all} = 1 if $c->model ( 'App::UserToPreference' )->find ( $user_h->{id}, "any_${whatever}_event_mail" );

      push @users, $h;
    }

    for ( $c->model ( 'App::UserToRole' )->search ({ role_id => "owns_all_$whateverz" }) ) {
      my $user_id = $_->user_id;

      my $found;

      for ( grep { $_->{id} eq $user_id } @users ) {
        $found = 1;

        $_->{owns_all} = 1;
      }

      unless ( $found ) {
        push @users, { id => $user_id, name => $_->user->name, fake => $_->user->fake, owns_all => 1 };
      }
    }

    for ( $c->model ( 'App::UserToRole' )->search ({ role_id => "writes_all_$whateverz" }) ) {
      my $user_id = $_->user_id;

      my $found;

      for ( grep { $_->{id} eq $user_id } @users ) {
        next unless $_->{id} eq $user_id;

        $found = 1;

        $_->{writes_all} = 1;
      }

      unless ( $found ) {
        push @users, { id => $user_id, name => $_->user->name, fake => $_->user->fake, writes_all => 1 };
      }
    }

    for ( $c->model ( 'App::UserToPreference' )->search ({ preference_id => "any_${whatever}_event_mail" }) ) {
      my $user_id = $_->user_id;

      my $found;

      for ( grep { $_->{id} eq $user_id } @users ) {
        $found = 1;

        $_->{follows_all} = 1;
      }

      unless ( $found ) {
        push @users, { id => $user_id, name => $_->user->name, fake => $_->user->fake, follows_all => 1 };
      }
    }

    if ( @users ) {
      $entity->{users} = [ sort { $a->{name} cmp $b->{name} } @users ];
    }
  }
  
  if ( $entity ) {
    for my $key ( users => @authorizations ) {
      for my $user_h ( @{$entity->{$key}} ) {
        for ( grep { ! defined $user_h->{$_} } keys %$user_h ) {
          delete $user_h->{$_};
        }
      }
    }
  }
  
  return $entity;
}

sub specification_commits {
  my ( $self, $option ) = @_;
  
  if ( my $svn = SOCK::SVN::Product->new ) {
    if ( my $tl_id = $svn->as_timeline ({ name => $option->{specification_id} }) ) {
      if ( my $commits = $tl_id->commits ) {
        for ( @$commits ) {
          $_->{date} = $_->{date}->iso8601;
          
          if ( my $user_r = $self->resultset ( 'User' )->find ( $_->{author} ) ) {
            $_->{name} = $user_r->name;
          }
          elsif ( my $user_r = $self->resultset ( 'User' )->search ({ subversion_id => $_->{author} })->first ) {
            $_->{name} = $user_r->name;
          }
        }
        
        return $commits;
      }
    }
  }
  
  return;
}

sub datasheet_commits {
  my ( $self, $option ) = @_;
  
  if ( my $svn = SOCK::SVN::Component->new ) {
    my ( $name, $number ) = split '_', $option->{datasheet_id};
    
    if ( my $tl_id = $svn->as_timeline ({ name => $name, number => $number }) ) {
      if ( my $commits = $tl_id->commits ) {
        for ( @$commits ) {
          $_->{date} = $_->{date}->iso8601;
          
          if ( my $user_r = $self->resultset ( 'User' )->find ( $_->{author} ) ) {
            $_->{name} = $user_r->name;
          }
          elsif ( my $user_r = $self->resultset ( 'User' )->search ({ subversion_id => $_->{author} })->first ) {
            $_->{name} = $user_r->name;
          }
        }
        
        return $commits;
      }
    }
  }
  
  return;
}

sub datasheet_products {
  my ( $self, $option ) = @_;
  
  if ( my $datasheet_id = $option->{datasheet_id} ) {
    if ( my $datasheet_r = $self->resultset ( 'Datasheet' )->find ( $datasheet_id ) ) {
      my $loh = { id => $datasheet_id };
      
      my $ahead    = $datasheet_r->ahead;
      my $last_tag = $datasheet_r->last_tag;
      
      $loh->{ahead}    = $ahead    if $ahead;
      $loh->{last_tag} = $last_tag if $last_tag;

      my @products;

      my @freeze_rs = map { $_->[1] } sort { ncmp ( $a->[0], $b->[0] ) } map { [ $_->get_column ( 'product_id' ), $_ ] } $self->resultset ( 'Freeze' )->search (
        {
          component_id                  => $datasheet_id,
          'component.type_id'           => 'datasheet',
          'products_to_freeze.scope_id' => 'specification',
        },
        {
          join      => [ { 'products_to_freeze' => 'product' }, 'component' ],
          '+select' => [ 'products_to_freeze.product_id', 'product.specification_id', 'product.specification_branch', 'product.stage_id' ],
          '+as'     => [                    'product_id',         'specification_id',         'specification_branch',         'stage_id' ],
          order_by  => [
            { -asc => 'products_to_freeze.product_id' },
            { -asc => 'component.id'                  },
            { -asc => 'products_to_freeze.scope_id'   },
          ],
        },
      )->all;

      if ( @freeze_rs ) {
        $loh->{products} = [ map { my $letter = $_->component_version ; {
          id                   => $_->get_column ( 'product_id'           ),
          stage                => $_->get_column ( 'stage_id'             ),
          specification_id     => $_->get_column ( 'specification_id'     ),
          specification_branch => $_->get_column ( 'specification_branch' ),
          component_version    => $letter,
          impacted             => ci_svn_tail ({
            xls_revision => $letter,
            svn_exists   => $self->resultset ( 'DatasheetTag' )->find ( $letter, $datasheet_id ),
            svn_latest   => $last_tag,
            svn_ahead    => $ahead,
          }) eq 'trunk' ? 1 : 0,
        } } @freeze_rs ];
      }
      else {
        $loh->{products} = [];
      }

      return $loh;
    }
    else {
      die "unknown \"$datasheet_id\" datasheet";
    }
  }
  else {
    die "missing datasheet ID";
  }
}

sub components_report {
  my ( $self, $option ) = @_;
  
  my $c = $option->{c};
  
  my $query = {
    'products_to_freeze.product_id' => [ map { { '=' => $_ } } @{$option->{products}} ],
    'products_to_freeze.scope_id'   => $option->{scope},
  };

  if ( $option->{types} and @{$option->{types}} ) {
    $query->{'component.type_id'} = [ map { { '=' => $_ } } @{$option->{types}} ],
  }
  
  my $freeze_rs = $self->resultset ( 'Freeze' )->search (
    $query,
    {
      join      => [ { 'products_to_freeze' => 'product' }, 'component' ],
      '+select' => [ 'products_to_freeze.product_id', 'product.specification_id', 'product.process_id', 'component.type_id' ],
      '+as'     => [                    'product_id',         'specification_id',         'process_id', 'component_type'    ],
      order_by  => [
        { -asc => 'component.id'                  },
        { -asc => 'products_to_freeze.product_id' },
      ],
    },
  );

  my @components;

  for my $freeze_r ( $freeze_rs->all ) {
    next unless $self->resultset ( 'Component' )->find ( $freeze_r->component_id )->type->bug_tracker_type_id eq 'bugzilla'; # FIXME use above JOIN instead?

    my @columns =                                      qw( id           version           type           product    specification    process    );
    my @values  = map { $freeze_r->get_column ( $_ ) } qw( component_id component_version component_type product_id specification_id process_id );

    my $h = { mesh @columns, @values };

    # FIXME check if process exists
    
    try {
      if ( my $sub_h = $self->freeze_tracker_h ( $c, $freeze_r ) ) {
        $h->{$_} = $sub_h->{$_} for keys %$sub_h;
      }
    }
    catch {
      $c->log->error ( "Caught exception on " . $freeze_r->component_id . " component!" );
      $c->log->error ( $_ );
    };

    push @components, $h;
  }
  
  return \@components;
}

=head2 freeze_tracker_h

  $hashref = $self->freeze_tracker_h ( $c, $freeze_r )

For a given L<iSOCK::Schema::App::Result::Freeze> instance, returns a hashref like:

  {
    tracker_id             => $id,
    tracker_version        => $version,
    tracker_type           => 'bugzilla',
    tracker_id_exists      => $boolean,
    tracker_id_url         => $url,
    tracker_version_exists => $boolean,
    tracker_version_url    => $url,
    tracker_total          => $natural,
    tracker_open           => $natural,
  }

Beware! Requires extra "process_id" and "component_version" columns (via L<DBIx::Class::ResultSet/get_column> sub).

=cut

sub freeze_tracker_h {
  my ( $self, $c, $freeze_r ) = @_;
  
  my $h;
  
  my $type_id = $freeze_r->get_column ( 'component_type' );
  
  if ( my $class = $c->model ( 'Bugzilla' )->class ( $type_id ) ) {
    my $tracker_m = $c->model ( $class );

    try {
      my $tracker_name    = $tracker_m->component_name    ( $freeze_r->component_id, $freeze_r->get_column ( 'process_id'        ) );
      my $tracker_version = $tracker_m->component_version (                          $freeze_r->get_column ( 'component_version' ) );

      $h->{tracker_id}      = $tracker_name;
      $h->{tracker_version} = $tracker_version;
      $h->{tracker_type}    = 'bugzilla';

      my ( $tracker_name_exists, $tracker_version_exists, $tracker_name_url, $tracker_version_url, $tracker_total, $tracker_open );

      if ( my $tracker_product_r = $tracker_m->resultset ( 'Product' )->search ({ name => $tracker_name })->first ) {
        my $tracker_root = $self->resultset ( 'ComponentType' )->find ( $type_id )->bug_tracker_url;

        $tracker_name_exists = 1;
        $tracker_name_url    = $tracker_product_r->bug_list_url ({ root => $tracker_root });

        if ( $tracker_m->resultset ( 'Version' )->search ({ product_id => $tracker_product_r->id, value => $tracker_version })->single ) {
          $tracker_version_exists = 1;
          $tracker_version_url    = $tracker_product_r->bug_list_url ({ root => $tracker_root, version => $tracker_version });
        }

        my @bugs = $tracker_product_r->bugs;

        $tracker_total = scalar                           @bugs;
        $tracker_open  = scalar grep { ! $_->resolution } @bugs;
      }

      $h->{tracker_id_exists}      = 1                    if $tracker_name_exists;
      $h->{tracker_id_url}         = $tracker_name_url    if $tracker_name_url;
      $h->{tracker_version_exists} = 1                    if $tracker_version_exists;
      $h->{tracker_version_url}    = $tracker_version_url if $tracker_version_url;
      $h->{tracker_total}          = $tracker_total;
      $h->{tracker_open}           = $tracker_open;
    };
  }
  
  return $h;
}

sub components_report_to_xls_file {
  my ( $self, $option ) = @_;
  
  my ( $report, $file, $author ) = map { $option->{$_} } qw( report file author );
  
  if ( $report ) {
    if ( my $workbook_id = Spreadsheet::WriteExcel->new ( $file ) ) {
      my $version_string = iSOCK->version_string . " GUI to " . SOCK->version_string . " CLI";

      $workbook_id->set_properties (
        title    => "Components Report",
        author   => $author || 'iSOCK',
        comments => "automagically generated by $version_string",
      );

      my ( $worksheet_id, $format ) = add_work_sheet ({ workbook_id => $workbook_id, worksheet_name => 'Components' });
      
      my @widths = (  24,   12,     12,        12,        12,        14,           14,                14,             14             );
      my @heads  = ( 'ID', 'Type', 'Product', 'Process', 'Version', 'Tracker ID', 'Tracker Version', 'Tracker Open', 'Tracker Total' );

      $worksheet_id->set_column   ( $_, $_, $widths[$_]                 ) for 0 .. @widths - 1;
      $worksheet_id->write_string ( 0,  $_, $heads[$_], $format->{head} ) for 0 .. @heads  - 1;
      
      {
        my $row = 1;

        tie my $class, 'Tie::FlipFlop', qw( odd even );

        for my $component_h ( sort { $a->{component_type} cmp $b->{component_type} } @$report ) {
          my $cell_format = $class eq 'odd' ? $format->{odd} : $format->{even};

          _write_string   ( $worksheet_id => $row, 0, $component_h->{id},                                                                             $cell_format                        );
          _write_whatever ( $worksheet_id => $row, 1, $component_h->{type},                                                                           $cell_format                        );
          _write_string   ( $worksheet_id => $row, 2, $component_h->{product},                                                                        $cell_format                        );
          _write_whatever ( $worksheet_id => $row, 3, $component_h->{process},                                                                        $cell_format                        );
          _write_string   ( $worksheet_id => $row, 4, $component_h->{version},                                                                        $cell_format                        );
          _write_whatever ( $worksheet_id => $row, 5, $component_h->{tracker_id_exists}      ? 'yes' : 'no', $component_h->{tracker_id_exists}      ? $cell_format     : $format->{error} );
          _write_whatever ( $worksheet_id => $row, 6, $component_h->{tracker_version_exists} ? 'yes' : 'no', $component_h->{tracker_version_exists} ? $cell_format     : $format->{error} );
          _write_whatever ( $worksheet_id => $row, 7, $component_h->{tracker_open}  || '-',                  $component_h->{tracker_open}           ? $format->{error} : $cell_format     );
          _write_whatever ( $worksheet_id => $row, 8, $component_h->{tracker_total} || '-',                                                           $cell_format                        );

          $row++;
        }
        
        $worksheet_id->autofilter ( 0, 0, scalar ( @$report ) - 1, 3 );
      }

      $workbook_id->close;
    }
  }
  
  return;
}

sub product_to_component_bug_loh {
  my ( $self, $c, $product, $scope, $tracker ) = @_;
  
  my $process;
  
  if ( my $product_r = $self->resultset ( 'Product' )->find ( $product ) ) {
    unless ( $process = $product_r->process_id ) {
      die "unknown product process";
    }
  }
  else {
    die "unkown '$product' product!";
  }
  
  my $loh;
  
  my $freeze_rs = $self->resultset ( 'Freeze' )->search (
    {
      'products_to_freeze.product_id' => $product,
      'products_to_freeze.scope_id'   => $scope,
    },
    {
      join     => 'products_to_freeze',
      order_by => {
        -asc => 'products_to_freeze.freeze_id',
      },
    },
  );
  
  if ( $tracker && ( $tracker eq 'DSTP' ) ) {
    my $model_m = $c->model ( 'DSTP' );
    
    while ( my $freeze_r = $freeze_rs->next ) {
      my $type_r    = $freeze_r->component->type;
      my $name      = $freeze_r->component_id;
      my $version   = $freeze_r->component_version;
      my $dstp_name = $model_m->component_name ( $name );
      
      if ( my $component_r = $c->model ( 'App::Component' )->find ( $name ) ) {
        next unless my $issues = $model_m->issues ( $dstp_name, $version );
        
        push @$loh, @$issues;
      }
    }
  }
  else {
    while ( my $freeze_r = $freeze_rs->next ) {
      my $type_r       = $freeze_r->component->type;
      my $name         = $freeze_r->component_id;
      my $version      = $freeze_r->component_version;
      
      my $bugzilla_url = $type_r->bug_tracker_url;
      
      if ( my $model_m = $self->_bugzilla_model ( $c, $type_r->id ) ) {
        my $bz_name    = $model_m->component_name    ( $name, $process );
        my $bz_version = $model_m->component_version ( $version );

        my @columns    = qw( bug_id bug_severity bug_status resolution component_id priority target_milestone short_desc );

        my $product_rs = $model_m->resultset ( 'Product' )->search (
          {
            name           => $bz_name,
            'bugs.version' => $bz_version,
          },
          {
            join      => 'bugs',
            '+select' => [ map { "bugs.$_" } @columns ],
            '+as'     =>                    \@columns,
          },
        );

        try {
          while ( my $product_r = $product_rs->next ) {
            my $bug_id = $product_r->get_column ( 'bug_id' );
            
            my $component;
            try {
              $component = $model_m->resultset ( 'Component' )->find ( $product_r->get_column ( 'component_id' ) )->name;
            }
            catch {
              $component = '-';
            };
            
            push @$loh, {
              id          => { tag => 'a', href => "$bugzilla_url/show_bug.cgi?id=$bug_id", content => $product_r->get_column ( 'bug_id' ) },
              type        => $freeze_r->component_type,
              category    => $component,
              surname     => $bz_name,
              name        => $product_r->bug_list_a ({ root => $bugzilla_url, content => $name                                                             }),
              version     => $product_r->bug_list_a ({ root => $bugzilla_url, content => $version, version => $bz_version, title => "$bz_name $bz_version" }),
              severity    => $product_r->get_column ( 'bug_severity'     ),
              priority    => $product_r->get_column ( 'priority'         ),
              milestone   => normalized_version_string ( $product_r->get_column ( 'target_milestone' ) ) || '',
              status      => $product_r->get_column ( 'bug_status'       ),
              resolution  => $product_r->get_column ( 'resolution'       ),
              description => $product_r->get_column ( 'short_desc'       ),
              comment     => join "\n\n---\n\n", grep { $_ } map { $_->thetext } $model_m->resultset ( 'Longdesc' )->search ( { bug_id => $bug_id }, { order_by => 'comment_id' } ), # FIXME comments list instead
            };
          }
        }
        catch {
          die "problem while accessing bugzilla product database: $_";
        };
      }
      else {
        $c->log->warn ( "no model for '" . $type_r->id . "' type" );
        
        next;
      }
    }
  }

  return $loh;
}

sub _bugzilla_model {
  my ( $self, $c, $type ) = @_;
  
  if ( my $class = $c->model ( 'Bugzilla' )->class ( $type ) ) {
    return $c->model ( $class );
  }
  
  return;
}

sub hit {
  my ( $self, $c, $type, $id ) = @_;
  
  if ( $type && $id ) {
    if ( $c->model ( 'App::' . ucfirst $type )->find ( $id ) ) {
      if ( my $hit_r = $c->model ( 'App::Hit' )->find ( $id ) ) {
        $hit_r->increment;
      }
      else {
        try {
          $c->model ( 'App::Hit' )->create ({ id => $id, type_id => $type });
        }
        catch {
          $c->log->error ( "[Model::App::hit] Caught an exception (\"" . last_breath ( $_ ) . "\")!" );
        };
      }
    }
  }
  
  return $self;
}

sub found_products_loh {
  my ( $self, $option ) = @_;
  
  my $parameter = $option->{parameter};
  
  my $query = {};
  
  {
    my $component_id_q   = $parameter->{component_id_is}   || ( $parameter->{component_id_like}   ? { like => $parameter->{component_id_like}   } : undef );
    my $component_type_q = $parameter->{component_type_is} || ( $parameter->{component_type_like} ? { like => $parameter->{component_type_like} } : undef );
    my $scope_q          = $parameter->{scope_is}          || ( $parameter->{scope_like}          ? { like => $parameter->{scope_like}          } : undef );

    $query->{'component.id'}                = $component_id_q   if $component_id_q;
    $query->{'component.type_id'}           = $component_type_q if $component_type_q;
    $query->{'products_to_freeze.scope_id'} = $scope_q          if $scope_q;
  }
  
  my $freeze_rs = $self->resultset ( 'Freeze' )->search ( $query,
    {
      join      => [ { 'products_to_freeze' => 'product' }, 'component' ],
      '+select' => [ 'products_to_freeze.product_id', 'products_to_freeze.scope_id', 'product.specification_id', 'component.id', 'component.type_id', 'me.component_version', 'product.process_id' ],
      '+as'     => [                    'product_id',                    'scope_id',         'specification_id', 'component_id', 'component_type',       'component_version',         'process_id' ],
      order_by  => [
        { -asc => 'products_to_freeze.product_id' },
        { -asc => 'component.id'                  },
        { -asc => 'products_to_freeze.scope_id'   },
      ],
    },
  );

  my @loh;

  while ( my $freeze_r = $freeze_rs->next ) {
    my $h = { map { $_ => $freeze_r->get_column ( $_ ) } qw( product_id scope_id component_id component_type component_version process_id ) };
    
    if ( my $sub_h = $self->freeze_tracker_h ( $option->{c}, $freeze_r ) ) {
      $h->{$_} = $sub_h->{$_} for keys %$sub_h;
    }
    
    push @loh, $h;
  }
  
  unless ( $parameter->{debug} ) {
    @loh = grep { defined $_->{product_id} } @loh;
  }
  
  return [ sort { ncmp ( $a->{product_id}, $b->{component_id} ) } @loh ];
}

sub specification_ahead_datasheets_as_loh {
  my ( $self, $specification_id ) = @_;
  
  if ( my $specification_r = $self->resultset ( 'Specification' )->find ( $specification_id ) ) {
    if ( my ( $product_r ) = $specification_r->products ) {
      return $self->product_ahead_datasheets_as_loh ( $product_r->id );
    }
  }
  
  return;
}

sub specification_ahead_datasheets_as_lol {
  my ( $self, $specification_id ) = @_;
  
  if ( my $specification_r = $self->resultset ( 'Specification' )->find ( $specification_id ) ) {
    if ( my ( $product_r ) = $specification_r->products ) {
      return $self->product_ahead_datasheets_as_lol ( $product_r->id );
    }
  }
  
  return;
}

sub specification_ahead_datasheets_as_tat {
  my ( $self, $specification_id ) = @_;
  
  if ( my $lol = $self->specification_ahead_datasheets_as_lol ( $specification_id ) ) {
    return lol_as_tat ( $lol );
  }
  
  return;
}

sub product_ahead_datasheets_as_loh {
  my ( $self, $product_id ) = @_;
  
  if ( my @datasheets = grep { $_->{impacts} } $self->product_datasheets ({ product_id => $product_id }) ) {
    my $loh = [ [ qw( ID Requested Published Description ) ] ];

    push @$loh, map { { id => $_->{id}, next_tag => $_->{version}, last_tag => $_->{svn_last}, description => $_->{description} } } @datasheets;

    return $loh;
  }
  
  return;
}

sub product_ahead_datasheets_as_lol {
  my ( $self, $product_id ) = @_;
  
  if ( my @datasheets = grep { $_->{impacts} } $self->product_datasheets ({ product_id => $product_id }) ) {
    my $lol = [ [ qw( ID Requested Published Description ) ] ];

    push @$lol, map { [ $_->{id}, $_->{version}, $_->{svn_last}, $_->{description} ] } @datasheets;

    return $lol;
  }
  
  return;
}

sub product_ahead_datasheets_as_tat {
  my ( $self, $product_id ) = @_;
  
  if ( my $lol = $self->product_ahead_datasheets_as_lol ( $product_id ) ) {
    return lol_as_tat ( $lol );
  }
  
  return;
}

sub products_x_ahead_datasheets_as_lol {
  my ( $self ) = @_;
  
  my $p2d;

  for my $datasheet_r ( sort { ncmp ( $a->id, $b->id ) } $self->resultset ( 'Datasheet' )->search ( { ahead => 1 }, { order_by => 'id' } ) ) {
    my $datasheet_id = $datasheet_r->id;

    next unless my $loh = $self->datasheet_products ({ datasheet_id => $datasheet_id } );

    $p2d->{$_->{id}}->{$datasheet_id} = $_->{component_version} for grep { $_->{impacted} } @{$loh->{products}};
  }
  
  if ( $p2d ) {
    return x_to_y_hash_as_lol ( $p2d, sub { return $_[0] || '-' } );
  }
  
  return $p2d;
}

sub pull_devices {
  my ( $self, $option ) = @_;
  
  my @loh;
  
  if ( my $pub = SOCK::PUB->new ({ path => $option->{c}->config->{directory}->{www} }) ) {
    for my $device_r ( $self->resultset ( 'Device' )->search ) {
      if ( my $hash = $pub->device_item_hash ({ id => $device_r->id }) ) {
        unless ( $option->{dummy} ) {
          $device_r->update ({ description => $hash->{description} }) if $hash->{description};
          $device_r->update ({ url         => $hash->{url}         }) if $hash->{url};
        }
        
        push @loh, $hash;
      }
    }
  }
  else {
    die 'Could not create SOCK::PUB instance!';
  }
  
  return [ sort { ncmp ( $a->{id}, $b->{id} ) } @loh ];
}

sub devices_loh {
  my ( $self ) = @_;
  
  my @loh;
  
  for my $device_r ( $self->resultset ( 'Device' )->search ) {
    my $h = { id => $device_r->id };
    
    $h->{description}      = $device_r->description if $device_r->description;
    $h->{url}              = $device_r->url         if $device_r->url;
    
    if ( my $line_id = $device_r->line_id ) {
      $h->{line_id}          = $line_id;
      $h->{line_description} = $device_r->line->description if $device_r->line->description;
    }
    
    {
      my ( @products, @specifications );

      for my $p2d_r ( $self->resultset ( 'ProductToDevice' )->search ( { device_id => $device_r->id }, { order_by => 'product_id' } ) ) {
        push @products,       $p2d_r->product_id;
        push @specifications, $p2d_r->product->specification_id;
      }

      if ( @products && @specifications ) {
        $h->{product_id}       = join ', ', @products;       # FIXME use arrayref?
        $h->{specification_id} = join ', ', @specifications; # FIXME ditto
      }
    }
    
    push @loh, $h;
  }
  
  return [ sort { ncmp ( $a->{id}, $b->{id} ) } @loh ];
}

sub last_real_job_r {
  my ( $self, $specification_id ) = @_;
  
  return $self->resultset ( 'Job' )->search ( { -and => [ { specification_id => $specification_id }, $self->not_bool ( 'dummy' ) ] }, { order_by => { -desc => 'id' } } )->first;
}

sub publishable_product_rs {
  my ( $self ) = @_;
  
  my @product_rs;
  
  for my $product_r ( $self->staged_product_rs ( 'DR4' ) ) {
    next unless my $job_r     = $self->last_real_job_r ( $product_r->specification_id );
    next unless my $status_id = $job_r->status_id;

    next if $status_id ne 'done';

    push @product_rs, $product_r;
  }
  
  return @product_rs;
}

sub staged_product_rs {
  my ( $self, $stage_id_gte ) = @_;
  
  my @product_rs;
  
  for my $product_r ( $self->resultset ( 'Product' )->search ( { -and => [ $self->not_bool ( 'dead' ) ] }, { order_by => 'id' } ) ) {
    next unless my $specification_id = $product_r->specification_id;
    next unless my $stage_id         = $product_r->stage_id;

    next if $stage_id lt $stage_id_gte;

    push @product_rs, $product_r;
  }
  
  return @product_rs;
}

sub harvestable_lines {
  my ( $self, $option ) = @_;
  
  my $dr_gte = $option->{dr_gte} ||= 'DR4';
  
  my ( $hoh, $device, $specification, $product );
  
  for my $line_r ( $self->resultset ( 'Line' )->search ( {}, { order_by => 'id' } ) ) {
    my $line_id = $line_r->id;
        
    my @devices;
    
    for my $device_r ( $line_r->devices ) {
      for my $p2d_r ( $device_r->product_to_devices ) {
        next unless my $dr = $p2d_r->product->stage_id;
        
        next if $dr lt $dr_gte;
        
        my $product_r        = $p2d_r->product;
        my $product_id       = $p2d_r->product_id;
        my $specification_id = $product_r->specification_id;
        
        next if $product_r->dead;
        
        push @devices, $device_r->id;
        
        $device->{$device_r->id} = { product => $product_id, specification => $specification_id, line => $line_id };
        
        $product->{$product_id}             = {};
        $specification->{$specification_id} = { product => $product_id, status => $dr };
        
        next unless my $job_r  = $self->last_real_job_r ( $specification_id );
        next unless my $status = $job_r->status_id;
        
        $specification->{$specification_id}->{status} = $status;
        
        next unless $status eq 'done' || $status eq 'waived';
        
        if ( my $svn = SOCK::SVN::Product->new ) {                                                                       # FIXME handle different repository
          $specification->{$specification_id}->{url} = $svn->url ({ name => $specification_id, view => 'integration' });
        }
      }
    }
    
    next unless @devices;
    
    $hoh->{line}->{$line_id}->{devices} = [ nsort @devices ];
  }
  
  for my $product_id ( keys %$product ) {
    next unless my $product_r = $self->resultset ( 'Product' )->find ( $product_id );
    
    $product->{$product_id} = { stage => $product_r->stage_id, specification => $product_r->specification_id };
  }
  
  $hoh->{device}        = $device        if $device;
  $hoh->{specification} = $specification if $specification;
  $hoh->{product}       = $product       if $product;
  
  return $hoh;
}

#
# True.
#

1;
